use chrono::{Datelike, Days, Months, NaiveDate};
use clickhouse_rs::ClientHandle;

use crate::base::{get_ch_pool, DiviFactConv, DiviType, Table};
pub struct AcblBack<'a> {
    divi_type: DiviType,
    back_date: &'a NaiveDate,
    tconf: &'a Table,
    tmp_table: String,
}
impl<'a> AcblBack<'a> {
    pub(super) fn new(divi_type: DiviType, back_date: &'a NaiveDate, tconf: &'a Table) -> Self {
        let acbl_procs = tconf.acbl_procs.as_ref().unwrap();
        let tmp_table = format!("TMP_{}", &acbl_procs.acbl_table_name.as_str()[2..]);
        Self {
            divi_type,
            tconf,
            back_date,
            tmp_table,
        }
    }
    async fn collapsing(
        &self,
        client: &mut ClientHandle,
        sdate: &NaiveDate,
        edate: &NaiveDate,
        dfc: &DiviFactConv,
        ins_col_names_str: &str,
        sel_col_names_str: &str,
        key_col_name: &str,
        where_col_name: Option<&String>,
    ) -> anyhow::Result<()> {
        let ap = self.tconf.acbl_procs.as_ref().unwrap();
        let tname = &self.divi_type.back_table_name();
        let kcn = key_col_name;
        let and_where = if let Some(wcn) = where_col_name {
            let wcv = dfc.where_col_val.as_deref().unwrap();
            format!("and {wcn}='{wcv}'")
        } else {
            String::new()
        };
        let sdate_str = sdate.format("%Y-%m-%d").to_string();
        let edate_str = edate.format("%Y-%m-%d").to_string();
        let sql_str = format!(
            r#"insert into {}({ins_col_names_str},DIVI.DPID,DIVI.EMID,DIVI.DSVL,SIGN)
            select {sel_col_names_str},anyLast(DIVI.DPID),anyLast(DIVI.EMID),anyLast(DIVI.DSVL),-1 from {}
            where DTID >= toDate('{sdate_str}') and DTID >= toDate('{edate_str}') and {kcn} in (select ACID from {tname})
            group by DTID,{key_col_name}
            having sum(SIGN)>0 {and_where}"#,
            self.tmp_table, ap.acbl_table_name
        );
        client.execute(&sql_str).await?;
        Ok(())
    }
    async fn insert(
        &self,
        client: &mut ClientHandle,
        sdate: &NaiveDate,
        edate: &NaiveDate,
        dfc: &DiviFactConv,
        ins_col_names_str: &str,
        sel_col_names_str: &str,
        key_col_name: &str,
        where_col_name: Option<&String>,
    ) -> anyhow::Result<()> {
        let ap = self.tconf.acbl_procs.as_ref().unwrap();
        let kcn = key_col_name;
        let back_dict_name = self.divi_type.back_dict_name();
        let dpids = format!("dictGet('{back_dict_name}','DIVI.DPID',{kcn},DTID)");
        let emids = format!("dictGet('{back_dict_name}','DIVI.EMID',{kcn},DTID)");
        let dsvls = format!("dictGet('{back_dict_name}','DIVI.DSVL',{kcn},DTID)");
        let tname = self.divi_type.back_table_name();
        let and_where = if let Some(wcn) = where_col_name {
            let wcv = dfc.where_col_val.as_deref().unwrap();
            format!("and {wcn}='{wcv}'")
        } else {
            String::new()
        };
        let sdate_str = sdate.format("%Y-%m-%d").to_string();
        let edate_str = edate.format("%Y-%m-%d").to_string();
        let sql_str = format!(
            r#"insert into {}({ins_col_names_str},DIVI.DPID,DIVI.EMID,DIVI.DSVL,SIGN)
            select {sel_col_names_str},{dpids},{emids},{dsvls},1
            from {} where DTID >= toDate('{sdate_str}') and DTID >= toDate('{edate_str}') and {kcn} in (select {kcn} from {tname})
            group by DTID,{key_col_name}
            having sum(SIGN)>0 {and_where}"#,
            self.tmp_table, ap.acbl_table_name
        );
        client.execute(&sql_str).await?;
        Ok(())
    }
    pub(super) async fn run(&self) -> anyhow::Result<()> {
        let mut dfc: Option<&DiviFactConv> = None;
        for d in self.tconf.divi_fact_convs.iter() {
            if d.divi_type == self.divi_type {
                dfc = Some(d);
                break;
            }
        }
        if dfc.is_none() {
            return Ok(());
        }
        let dfc = dfc.unwrap();
        let ap = self.tconf.acbl_procs.as_ref();
        if ap.is_none() {
            return Ok(());
        }
        let ap = ap.unwrap();
        let mut ins_col_names: Vec<&str> = Vec::new();
        let mut sel_col_names: Vec<String> = Vec::new();
        let key_col_name = ap
            .col_map
            .iter()
            .find(|(_, v)| *v == dfc.key_col_name.as_str())
            .map(|(k, _)| k);
        if key_col_name.is_none() {
            Err(anyhow::anyhow!(
                "导入{}定义错误，在acbl_procs中没有找到divi_fact_convs中定义的key_col_name",
                self.divi_type
            ))?;
        }
        let key_col_name = key_col_name.unwrap();
        for (k, _v) in ap.col_map.iter() {
            ins_col_names.push(k);
            if k == "DTID" || k == key_col_name {
                sel_col_names.push(k.to_owned());
            } else {
                sel_col_names.push(format!("anyLast({k}) as {k}"));
            }
        }
        let ins_col_names_str = ins_col_names.join(",");
        let sel_col_names_str = sel_col_names.join(",");
        let ap = self.tconf.acbl_procs.as_ref().unwrap();
        let atn = ap.acbl_table_name.as_str();
        let sql = format!("select max(DTID) from {atn}");
        let mut client = get_ch_pool().get_handle().await?;
        let b = client.query(&sql).fetch_all().await?;
        let max_date: NaiveDate = b.rows().next().unwrap().get(0)?;
        let where_col_name = ap
            .col_map
            .iter()
            .find(|(_, v)| Some(v.as_str()) == dfc.where_col_name.as_deref())
            .map(|(k, _)| k);
        if dfc.where_col_name.is_some() && where_col_name.is_none() {
            Err(anyhow::anyhow!(
                "导入{}定义错误，在acbl_procs中没有找到divi_fact_convs中定义的where_col_name",
                self.divi_type
            ))?;
        }
        let sql = format!(
            "SYSTEM RELOAD DICTIONARY {}",
            self.divi_type.back_dict_name()
        );
        client.execute(sql).await?;
        let sql = format!(
            "CREATE TABLE IF NOT EXISTS {} AS {}",
            self.tmp_table, ap.acbl_table_name
        );
        client.execute(&sql).await?;
        let mut curr_date =
            NaiveDate::from_ymd_opt(self.back_date.year(), self.back_date.month(), 1).unwrap();
        while curr_date <= max_date {
            let sdate = if curr_date > *self.back_date {
                &curr_date
            } else {
                self.back_date
            };
            let edate = curr_date
                .checked_add_months(Months::new(1))
                .unwrap()
                .checked_sub_days(Days::new(1))
                .unwrap();
            let sql = format!("TRUNCATE TABLE {}", self.tmp_table);
            client.execute(&sql).await?;
            self.collapsing(
                &mut client,
                sdate,
                &edate,
                dfc,
                &ins_col_names_str,
                &sel_col_names_str,
                key_col_name,
                where_col_name,
            )
            .await?;
            self.insert(
                &mut client,
                sdate,
                &edate,
                dfc,
                &ins_col_names_str,
                &sel_col_names_str,
                key_col_name,
                where_col_name,
            )
            .await?;
            let sql = format!(
                "insert into {} select * from {} order by SIGN",
                ap.acbl_table_name, self.tmp_table
            );
            client.execute(&sql).await?;
            curr_date = curr_date.checked_add_months(Months::new(1)).unwrap();
        }
        let sql = format!("DROP TABLE {}", self.tmp_table);
        client.execute(&sql).await?;
        Ok(())
    }
}
